from PyQt5.QtCore import pyqtSlot
from PyQt5.QtWidgets import QMainWindow, QMessageBox
from PyQt5 import QtWidgets
from Ui_risk_test_mainwindow import Ui_MainWindow
import os
import stat
import paramiko  # SFTP\SHH 与linxu建立连接
import sys
from subprocess import Popen, PIPE


class MainWindow_slots(QMainWindow, Ui_MainWindow):  # 继承QMainWindow Ui_MainWindow父类
    """
    Class documentation goes here.
    """

    def __init__(self, parent=None):
        """
        Constructor

        @param parent reference to the parent widget
        @type QWidget
        """
        super(MainWindow_slots, self).__init__(parent)  # 把子类MainWindow_slots的实例化对象self在父类(QMainWindow, Ui_MainWindow)中初始化，以继承父类的属性和方法
        self.setupUi(self)  # 父类中的setupUi()方法

    @pyqtSlot()
    def on_pushButton_clicked(self):  # 槽函数 命名规则 on_控件名_信号(self)
        v_account_no = self.lineEdit_5.text()
        if v_account_no == '':
            # 使用QMessageBox的警告提示框提示信息
            self.message_warning = QMessageBox.warning(self, 'Warning', '请输入账户编号与对应帐户级别', QMessageBox.Yes,
                                                       QMessageBox.No)
        else:
            self.read_file()

    @pyqtSlot()
    def on_pushButton_2_clicked(self):
        self.textBrowser.setText('')

    def read_file(self):
        # 删除linux中的文件
        try:  # try except finally 三段式
            self.delete_file(host, port, username, password, remote)
        except Exception as e:
            print("Delete File Exception:", e)
            log = "Delete File Exception:" + e
            self.textBrowser_2.append(log)
        finally:
            pass
        # 开始计算
        try:
            self.calculate_creat_file()
        except Exception as e:
            print('Creat_File Exception:', e)
            log = "Creat_File Exception:" + e
            self.textBrowser_2.append(log)
        finally:
            pass
        # 下载linux remote目录中文件到指定目录local
        try:
            self.sftp_download(host, port, username, password, local, remote)  # 调用下载的方法，下载的方法在read_file()所属的类之外
        except Exception as e:
            print('Sftp_download Exception:', e)
            log = "Sftp_download Exception:" + e
            self.textBrowser_2.append(log)
        finally:
            pass
        # 将文件内容放到textBrowser
        try:
            file_address = os.path.abspath('.') + '\\download_file\\myfile_name'  # 此路径为默认下载路径
            file = open(file_address, 'r')
            text_textbrowser = file.read()
            self.textBrowser.append(text_textbrowser)
        except Exception as e:
            print('Read File Exception:', e)
            log = "Read File Exception:" + e
            self.textBrowser_2.append(log)
        finally:
            if os.path.exists(file_address):
                file.close()
            else:
                pass
                # 文件删除

    def delete_file(self, host, port, username, password, remote):
        ssh = paramiko.SSHClient()  # 创建一个SSHClient的实例化对象
        try:
            # 下面代码的作用是允许连接不在know_hosts文件中的主机。
            ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            ssh.connect(host, port, username, password)  # ssh创建连接
            command = 'rm -rf ' + remote
            stdin, stdout, stderr = ssh.exec_command(command)  # 执行linux命令
            # str = stdout.readlines()  # 返回linxu的应答 list类型
            # print(str)
        except Exception as e:
            print("SSHConnect Exception:", e)
            log = "SSHConnect Exception:" + e
            self.textBrowser_2.append(log)
        finally:
            ssh.close()
            # 调用sqlplus执行存储过程

    def calculate_creat_file(self):
        # 账户
        v_account_no = self.lineEdit_5.text()
        v_account_level = '2'
        v_up_or_down = '1'  # 上限
        v_flag_surplus = '1'  # 计算余券
        if self.radioButton_5.isChecked():
            v_account_level = '0'  # 交易单元级
        if self.radioButton_6.isChecked():
            v_account_level = '2'  # 产品级
        if self.radioButton.isChecked():
            v_up_or_down = '1'  # 上限
        if self.radioButton_2.isChecked():
            v_up_or_down = '0'  # 下限
        if self.radioButton_3.isChecked():
            v_flag_surplus = '1'  # 计算余券
        if self.radioButton_4.isChecked():
            v_flag_surplus = '0'  # 不计算余券
        # 使用tabWidget类的currentIndex()方法，获取当前选中的tab页的Index
        if self.tabWidget.currentIndex() == 0:
            risk_type = '0'  # 事前风控标签页选中
        elif self.tabWidget.currentIndex() == 1:
            risk_type = '1'  # 事中风控标签页选中
        if risk_type == '0':  # 事前风控
            try:
                sql = "EXEC PKG_PTRISK_EXEC.PTRISK_BEFOR_EXECUTABLE_SCRIPT(p_account_no => " + v_account_no \
                      + ", p_account_level => " + v_account_level \
                      + ", p_up_or_down => " + v_up_or_down \
                      + ", p_flag_surplus => " + v_flag_surplus \
                      + ")"
                proc = Popen(["sqlplus", "*/*@*/*"], stdout=PIPE, stdin=PIPE,
                             stderr=PIPE)  # 模拟win系统上cmd命令 sqlplus输入
                proc.stdin.write(bytes(sql, encoding='gbk'))
                (out, err) = proc.communicate()
                if proc.returncode != 0:
                    print('err:', str(err, encoding='gbk'))
                    log = "SQLPlus ERR:" + str(err, encoding='gbk')
                    self.textBrowser_2.append(log)

                else:
                    print('out:', str(out, encoding='gbk'))  # 用GBK编码转换成字符串
                    log = "SQLPlus OUT:" + str(out, encoding='gbk')
                    self.textBrowser_2.append(log)
            except Exception as e:
                print("SQLPlus EXEC Exception:", e)
                log = "SQLPlus EXEC Exception:" + e
                self.textBrowser_2.append(log)
            finally:
                pass
        elif risk_type == '1':  # 事中风控
            try:
                sql = "EXEC PKG_PTRISK_EXEC.PTRISK_EXECUTABLE_SCRIPT(p_account_no => " + v_account_no \
                      + ", p_account_level => " + v_account_level \
                      + ")"
                proc = Popen(["sqlplus", "*/*@*/*"], stdout=PIPE, stdin=PIPE,
                             stderr=PIPE)  # 模拟win系统上cmd命令 sqlplus输入
                proc.stdin.write(bytes(sql, encoding='gbk'))
                (out, err) = proc.communicate()
                if proc.returncode != 0:
                    print('err:', str(err, encoding='gbk'))
                    log = "SQLPlus ERR:" + str(err, encoding='gbk')
                    self.textBrowser_2.append(log)

                else:
                    print('out:', str(out, encoding='gbk'))  # 用GBK编码转换成字符串
                    log = "SQLPlus OUT:" + str(out, encoding='gbk')
                    self.textBrowser_2.append(log)
            except Exception as e:
                print("SQLPlus EXEC Exception:", e)
                log = "SQLPlus EXEC Exception:" + e
                self.textBrowser_2.append(log)

    # ------从Linux下载文件------
    def sftp_download(self, host, port, username, password, local, remote):
        sf = paramiko.Transport(host, port)
        sf.connect(username=username, password=password)
        sftp = paramiko.sftp_client.SFTPClient.from_transport(sf)
        try:
            if os.path.exists(local):
                pass
            else:
                os.mkdir(local)
            try:
                file_exit_flag = '1'
                SFTPAttributes_files = sftp.lstat(remote)
            except Exception as e:
                print('文件不存在，路径:', remote)
                log = '文件不存在，路径:' + remote
                self.textBrowser_2.append(log)
                file_exit_flag = '0'
            if file_exit_flag == '1':
                if stat.S_ISDIR(SFTPAttributes_files.st_mode):
                    all_files = get_all_files_in_remote_dir(sftp, remote)
                    for f in all_files:  # 遍历远程目录
                        f_name = f.split('/')[-1]  # 以'/'进行分割，获取列表中最后的元素作为文件名
                        sftp.get(os.path.join(remote, f),
                                 os.path.join(local, f_name))  # 下载目录中文件，注意该目录下不允许出现目录。get方法只是用来下载路径下的文件
                else:
                    f_name = remote.split('/')[-1]
                    sftp.get(remote, os.path.join(local, f_name))  # 下载目录中文件，注意该目录下不允许出现目录。get方法只是用来下载路径下的文件
        except Exception as e:
            print('Download Exception:', e)
            log = "Download Exception:" + e
            self.textBrowser_2.append(log)
        finally:
            sf.close()


# ------获取远端linux主机上指定目录及其子目录下的所有文件------
def get_all_files_in_remote_dir(sftp, remote):
    # 保存所有文件的列表
    all_files = list()

    # 去掉路径字符串最后的字符'/'，如果有的话
    if remote[-1] == '/':
        remote = remote[0:-1]

    # 获取当前指定目录下的所有目录及文件，包含属性值
    files = sftp.listdir_attr(remote)
    for x in files:
        # remote目录中每一个文件或目录的完整路径
        filename = remote + '/' + x.filename
        # 如果是目录，则递归处理该目录，这里用到了stat库中的S_ISDIR方法，与linux中的宏的名字完全一致
        if stat.S_ISDIR(x.st_mode):
            all_files.extend(get_all_files_in_remote_dir(sftp, filename))
        else:
            all_files.append(filename)
    return all_files


if __name__ == '__main__':
    host = '*'  # 主机
    port = 22  # 端口
    username = 'oracle'  # 用户名
    password = '*'  # 密码
    local = os.path.abspath('.') + '\\download_file'  # 本地文件或目录，与远程一致，当前为windows目录格式，window目录中间需要使用双斜线
    remote = '/home/ptrisk_test/myfile_name'  # 远程文件或目录，与本地一致，当前为linux目录格式
    app = QtWidgets.QApplication(sys.argv)
    slot = MainWindow_slots()
    slot.show()
    sys.exit(app.exec_())